package sqlexplorer.application;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Observable;
import java.util.Observer;

import sqlexplorer.application.exceptions.NotConnectedException;
import sqlexplorer.domain.DatabaseType;
import sqlexplorer.domain.metadata.ColumnMetaData;
import sqlexplorer.domain.metadata.TableMetaData;
import sqlexplorer.domain.sorting.ColumnMetadataComparator;
import sqlexplorer.domain.sorting.SortDirection;
import sqlexplorer.domain.sorting.TableMetaDataComparator;

public class MetaDataController extends Observable implements Observer{
	
	private static MetaDataController m_instance;
	
	private SortDirection m_sortDirection;
	
	private MetaDataController(){
		m_sortDirection=SortDirection.ASCENDING;
		
		ConnectionsController.getInstance().addObserver(this);
	}
	
	public static MetaDataController getInstance(){
		if(m_instance==null){
			m_instance=new MetaDataController();
		}
		
		return m_instance;
	}
	
	public TableMetaData[] getMetaData() throws NotConnectedException,SQLException{
		Connection connection=ConnectionsController.getInstance().getConnection();
		ResultSet tablesResultSet=null;
		
		if(connection!=null){
			try{
				List<TableMetaData> tablesList=new ArrayList<TableMetaData>();
				
				DatabaseMetaData metaData=connection.getMetaData();
				
				//get the tables
				tablesResultSet=metaData.getTables(null,null,"%",null);
				
				while(tablesResultSet.next()){
					String tableName=tablesResultSet.getString(3);
					String tableType=tablesResultSet.getString(4);
					
					ResultSet columnsResultSet=null;
					
					try{
						//get the columns
						List<ColumnMetaData> columnsList=new ArrayList<ColumnMetaData>();
						
						columnsResultSet=metaData.getColumns(null,null,tableName,"%");
						
						while(columnsResultSet.next()){
							String columnName=columnsResultSet.getString(4);
							String typeName=columnsResultSet.getString(6);
							
							columnsList.add(new ColumnMetaData(columnName,typeName));
						}
						
						Collections.sort(columnsList,new ColumnMetadataComparator(m_sortDirection));
						
						tablesList.add(new TableMetaData(tableName,tableType,columnsList.toArray(new ColumnMetaData[0])));
					}catch(SQLException exc){
						System.out.println("Failed to get meta data for "+tableName);
					}finally{
						try{
							columnsResultSet.close();
						}catch(Exception exc){}
					}
				}
				
				Collections.sort(tablesList,new TableMetaDataComparator(m_sortDirection));
				
				return tablesList.toArray(new TableMetaData[0]);
			}finally{
				try{
					tablesResultSet.close();
				}catch(Exception exc){}
			}
		}else{
			throw new NotConnectedException();
		}
	}
	
	//Test: to connect direct with database
	public TableMetaData[] getMetaDataForConnection(String jdbcUrl,String userName,String password)throws ClassNotFoundException,SQLException{
		Connection connection=null;
		
		try{
			Class.forName(DatabaseType.getDriverName(DatabaseType.MYSQL));
			
			connection=DriverManager.getConnection(jdbcUrl,userName,password);
			

			List<TableMetaData> tablesList=new ArrayList<TableMetaData>();
			
			DatabaseMetaData metaData=connection.getMetaData();
			
			//get the tables
			ResultSet tablesResultSet=metaData.getTables(null,null,"%",null);
			
			while(tablesResultSet.next()){
				String tableName=tablesResultSet.getString(3);
				String tableType=tablesResultSet.getString(4);
				
				ResultSet columnsResultSet=null;
				
				try{
					//get the columns
					List<ColumnMetaData> columnsList=new ArrayList<ColumnMetaData>();
					
					columnsResultSet=metaData.getColumns(null,null,tableName,"%");
					
					while(columnsResultSet.next()){
						String columnName=columnsResultSet.getString(4);
						String typeName=columnsResultSet.getString(6);
						
						columnsList.add(new ColumnMetaData(columnName,typeName));
					}
					
					Collections.sort(columnsList,new ColumnMetadataComparator(m_sortDirection));
					
					tablesList.add(new TableMetaData(tableName,tableType,columnsList.toArray(new ColumnMetaData[0])));
				}catch(SQLException exc){
					System.out.println("Failed to get meta data for "+tableName);
				}finally{
					try{
						columnsResultSet.close();
					}catch(Exception exc){}
				}
			}
			
			Collections.sort(tablesList,new TableMetaDataComparator(m_sortDirection));
			
			return tablesList.toArray(new TableMetaData[0]);
		}finally{
			try{
				connection.close();
			}catch(Exception exc){}
		}
	}
	
	public void swapSortDirection(){
		if(m_sortDirection.equals(SortDirection.ASCENDING)){
			setSortDirection(SortDirection.DESCENDING);
		}else{
			setSortDirection(SortDirection.ASCENDING);
		}
	}
	
	public void setSortDirection(SortDirection sortDirection){
		if(!m_sortDirection.equals(sortDirection)){
			m_sortDirection=sortDirection;
			
			setChanged();
			notifyObservers("connectionChanged");
		}
	}
	
	@Override
	public void update(Observable observable, Object argument){
		if(observable instanceof ConnectionsController){
			if(argument.equals("connectionChanged")){
				setChanged();
				notifyObservers("connectionChanged");
			}else if(argument.equals("connectionCancelled")){
				setChanged();
				notifyObservers("connectionCancelled");
			}
		}
	}
}
